package cn.gbase.jiangsu.data.transfer.service;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Entity;
import org.nutz.dao.impl.sql.SqlTemplate;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.service.IdEntityService;

import cn.gbase.jiangsu.data.transfer.bean.BuildingUnit;
import cn.gbase.jiangsu.data.transfer.bean.Community;
import cn.gbase.jiangsu.data.transfer.bean.CommunityBuilding;
import cn.gbase.jiangsu.data.transfer.bean.Company;
import cn.gbase.jiangsu.data.transfer.bean.CompanyToBuilding;
import cn.gbase.jiangsu.data.transfer.bean.CompanyToSchoolUser;
import cn.gbase.jiangsu.data.transfer.bean.CompanyUser;
import cn.gbase.jiangsu.data.transfer.bean.OrderSupplierView;
import cn.gbase.jiangsu.data.transfer.bean.ProductOrder;
import cn.gbase.jiangsu.data.transfer.bean.RebateConfig;
import cn.gbase.jiangsu.data.transfer.bean.ShopUser;
import cn.gbase.jiangsu.data.transfer.bean.ShopUserView;
import cn.gbase.jiangsu.data.transfer.bean.ShopUsersTrade;
import cn.gbase.jiangsu.data.transfer.bean.Supplier;
import cn.gbase.jiangsu.data.transfer.bean.UnitRoom;
import cn.gbase.jiangsu.data.transfer.common.Constants;
import cn.gbase.jiangsu.data.transfer.vo.ReadyProductVO;

import com.google.common.collect.Maps;

@IocBean(args = { "refer:dao" })
public class CompanyService extends IdEntityService<Company> {

	private final static SimpleDateFormat sfd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	public CompanyService() {
		super();
	}

	public CompanyService(Dao dao) {
		super(dao);
	}

	/**
	 * 根据ID取得物业公司.
	 * 
	 * @param userId
	 * @return
	 */
	public Company getCompany(int sId) {
		return this.dao().fetch(Company.class, sId);
	}

	public List<Community> getCommunity(Integer cid) {
		List<Community> communityList = new ArrayList<Community>();

		List<CompanyToBuilding> ctb = getCompanyToCommunity(cid);
		if (ctb != null && ctb.size() > 0) {
			CompanyToBuilding companyToBuilding = ctb.get(0);
			CommunityBuilding building = this.dao().fetch(CommunityBuilding.class, companyToBuilding.getBuildingId());
			Community community = this.dao().fetch(Community.class, building.getCommunityId());
			if (community.getDeleteFlag() != 1)//没被删除
				communityList.add(community);
		} else {
			Company company = this.dao().fetch(Company.class, cid); // 当前页面显示记录
			Community communityReal = this.dao().fetch(Community.class, company.getCommunityId());
			communityList.add(communityReal);
		}
		return communityList;
	}

	public List<CompanyToBuilding> getCompanyToCommunity(Integer cid) {
		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);

		// id
		if (cid != null) {
			group = group.and("companyId", "=", cid);
		}

		List<CompanyToBuilding> cs = this.dao().query(CompanyToBuilding.class, Cnd.where(group));
		return cs;
	}

	public List<CommunityBuilding> getBuilding(Integer cid) {
		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);

		// id
		if (cid != null) {
			group = group.and("companyId", "=", cid);
		}

		List<CompanyToBuilding> cs = this.dao().query(CompanyToBuilding.class, Cnd.where(group));
		List<CommunityBuilding> buildingList = new ArrayList<CommunityBuilding>();
		for (CompanyToBuilding ctb : cs) {
			CommunityBuilding building = this.dao().fetch(CommunityBuilding.class, ctb.getBuildingId());
			if (building.getDeleteFlag() != 1)//没被删除
				buildingList.add(building);
		}
		return buildingList;
	}

	public List<BuildingUnit> getUnit(Integer bid) {
		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);

		// id
		if (bid != null) {
			group = group.and("buildingId", "=", bid).and("deleteFlag", "=", 0);
		}

		List<BuildingUnit> cs = this.dao().query(BuildingUnit.class, Cnd.where(group));

		return cs;
	}

	public List<UnitRoom> getRoom(Integer uid) {
		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);

		// id
		if (uid != null) {
			group = group.and("unitId", "=", uid).and("deleteFlag", "=", 0);
		}

		List<UnitRoom> cs = this.dao().query(UnitRoom.class, Cnd.where(group));

		return cs;
	}

	/**
	 * 删除用户.
	 * 
	 * @param userId
	 */
	public void deleteUser(int userId) {
		CompanyUser user = this.dao().fetch(CompanyUser.class, userId);
		user.setStatus(0);
		this.dao().update(user);
	}

	/**
	 * 取得属于供货商的订单列表
	 * 
	 * @param currentPage
	 * @return 订单列表
	 */
	public QueryResult getOrderList(int pageNumber, String orderCode, String status, String supplierName,
			List<CompanyToBuilding> buildingList, String startTime, String endTime,
			List<CompanyToSchoolUser> schoolUsers, String address) {

		List<Integer> cids = new ArrayList<Integer>();
		// 物业管辖的用户
		StringBuilder ids = new StringBuilder("");
		if (schoolUsers != null && schoolUsers.size() > 0) {
			for (CompanyToSchoolUser su : schoolUsers)
				ids.append(su.getUserId() + ",");
		} else {
			for (CompanyToBuilding com : buildingList)
				cids.add(com.getBuildingId());
			if (cids.size() > 0) {
				SqlExpressionGroup groupUser = Cnd.exps("buildingId", "in", cids);
				// 拼接条件
				List<ShopUserView> list = this.dao().query(ShopUserView.class, Cnd.where(groupUser), null);
				for (ShopUserView user : list)
					ids.append(user.getId() + ",");
			}
		}
		// 每页显示记录数
		int iPageSize = 15;

		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, iPageSize);
		//找实物，线上订单
		String hql = "select go.* from goods_order go INNER JOIN order_supplier_view fodv on fodv.order_id=go.id"
				+ " INNER JOIN supplier pro on fodv.supplier_id=pro.id where fodv.virtual_flag="
				+ Constants.VIRTUAL_NO_FLAG + " and pro.offline_flag=" + Constants.ON_LINE;
		HashMap map = new HashMap();
		// 拼接条件
		hql += " and go.order_type=" + Constants.PRODUCT_ORDER + " and go.show_flag=" + Constants.ORDER_STATUS_PAID
				+ " and go.status<>" + Constants.ORDER_STATUS_CANCEL + " and go.status<>"
				+ Constants.ORDER_STATUS_UNPAID;
		//		group = group.and("status", "<>", Constants.ORDER_DISPATCH_STATION);
		//		group = group.and("status", "<>", Constants.ORDER_STATUS_DONE);
		// 订单状态
		if (status != null && status.length() > 0) {
			hql += " and go.status=@status";
			map.put("status", status);
		}
		if (orderCode != null && orderCode.length() > 0) {
			hql += " and go.code like @orderCode";
			map.put("orderCode", "%" + orderCode + "%");
		}
		if (StringUtils.isNotBlank(address)) {
			hql += " and go.address like @address";
			map.put("address", "%" + address + "%");
		}
		//若没有管辖用户就什么都不查
		if (!"".equals(ids)) {
			hql += " and go.user_id in (" + ids.substring(0, ids.length() - 1) + ")";
		} else
			hql += " and 1=2";
		if (supplierName != null && supplierName.length() > 0) {
			hql += " and go.supplier_id=@supplierId";
			map.put("supplierId", supplierName);
		}
		// 下单时间
		if (startTime != null && startTime.length() > 0) {
			hql += " and go.create_time>@createTime";
			map.put("createTime", startTime + " 00:00:00");
		}

		// 下单时间
		if (endTime != null && endTime.length() > 0) {
			hql += " and go.create_time<@endTime";
			map.put("endTime", endTime + " 23:59:59");
		}
		hql += " order by go.create_time desc";
		Sql sql = Sqls.create(hql);
		if (map != null) {
			for (Object key : map.keySet())
				sql.params().set(key.toString(), map.get(key));
		}
		sql.setCallback(Sqls.callback.entities());
		/* 定义返回实体类型 */
		Entity<ProductOrder> entity = this.dao().getEntity(ProductOrder.class);

		sql.setEntity(entity);
		/* 执行 */
		this.dao().execute(sql);
		List<ProductOrder> allList = sql.getList(ProductOrder.class);
		pager.setRecordCount(allList.size());
		/* 设置分页 【如果按任务组显示，需要表格显示时开启】 */
		sql.setPager(pager);
		this.dao().execute(sql);
		List<ProductOrder> pageList = sql.getList(ProductOrder.class);

		QueryResult result = new QueryResult();

		result.setList(pageList);

		result.setPager(pager);
		// 返回
		return result;
	}

	public ShopUser getUser(String mobile) {
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		group = group.and("mobile", "=", mobile);
		List<ShopUser> query = this.dao().query(ShopUser.class, Cnd.where(group));
		if (query != null && query.size() > 0)
			return query.get(0);
		return null;
	}

	/**
	 * 取得属于供货商的订单列表
	 * 
	 * @param currentPage
	 * @return 订单列表
	 */
	public QueryResult getOldOrderList(int pageNumber, String orderCode, String status, String supplierName,
			List<CompanyToBuilding> buildingList, String startTime, String endTime,
			List<CompanyToSchoolUser> schoolUsers, String address) {

		List<Integer> cids = new ArrayList<Integer>();
		// 物业管辖的用户
		StringBuilder ids = new StringBuilder("");
		if (schoolUsers != null && schoolUsers.size() > 0) {
			for (CompanyToSchoolUser su : schoolUsers)
				ids.append(su.getUserId() + ",");
		} else {
			for (CompanyToBuilding com : buildingList)
				cids.add(com.getBuildingId());
			if (cids.size() > 0) {
				SqlExpressionGroup groupUser = Cnd.exps("buildingId", "in", cids);
				// 拼接条件
				List<ShopUserView> list = this.dao().query(ShopUserView.class, Cnd.where(groupUser), null);
				for (ShopUserView user : list)
					ids.append(user.getId() + ",");
			}
		}

		// 每页显示记录数
		int iPageSize = 15;

		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, iPageSize);

		//找实物，线上订单
		String hql = "select go.* from goods_order go INNER JOIN order_supplier_view fodv on fodv.order_id=go.id"
				+ " INNER JOIN supplier pro on fodv.supplier_id=pro.id where fodv.virtual_flag="
				+ Constants.VIRTUAL_NO_FLAG + " and pro.offline_flag=" + Constants.ON_LINE;
		HashMap map = new HashMap();
		// 拼接条件
		hql += " and go.order_type=" + Constants.PRODUCT_ORDER + " and go.show_flag=" + Constants.ORDER_STATUS_PAID
				+ " and go.status<>" + Constants.ORDER_STATUS_CANCEL;
		//		group = group.and("status", "<>", Constants.ORDER_DISPATCH_STATION);
		//		group = group.and("status", "<>", Constants.ORDER_STATUS_DONE);
		// 订单状态
		if (status != null && status.length() > 0) {
			hql += " and go.status=@status";
			map.put("status", status);
		}
		if (orderCode != null && orderCode.length() > 0) {
			hql += " and go.code like @orderCode";
			map.put("orderCode", "%" + orderCode + "%");
		}
		if (StringUtils.isNotBlank(address)) {
			hql += " and go.address like @address";
			map.put("address", "%" + address + "%");
		}
		//若没有管辖用户就什么都不查
		if (!"".equals(ids)) {
			hql += " and go.user_id in (" + ids.substring(0, ids.length() - 1) + ")";
		} else
			hql += " and 1=2";
		if (supplierName != null && supplierName.length() > 0) {
			hql += " and go.supplier_id=@supplierId";
			map.put("supplierId", supplierName);
		}
		// 下单时间
		if (startTime != null && startTime.length() > 0) {
			hql += " and go.create_time>@createTime";
			map.put("createTime", startTime + " 00:00:00");
		}

		// 下单时间
		if (endTime != null && endTime.length() > 0) {
			hql += " and go.create_time<@endTime";
			map.put("endTime", endTime + " 23:59:59");
		}
		hql += " order by go.create_time desc";
		Sql sql = Sqls.create(hql);
		if (map != null) {
			for (Object key : map.keySet())
				sql.params().set(key.toString(), map.get(key));
		}
		sql.setCallback(Sqls.callback.entities());
		/* 定义返回实体类型 */
		Entity<ProductOrder> entity = this.dao().getEntity(ProductOrder.class);

		sql.setEntity(entity);
		/* 执行 */
		this.dao().execute(sql);
		List<ProductOrder> allList = sql.getList(ProductOrder.class);
		pager.setRecordCount(allList.size());
		/* 设置分页 【如果按任务组显示，需要表格显示时开启】 */
		sql.setPager(pager);
		this.dao().execute(sql);
		List<ProductOrder> pageList = sql.getList(ProductOrder.class);

		QueryResult result = new QueryResult();

		result.setList(pageList);

		result.setPager(pager);
		// 返回
		return result;
	}

	/**
	 * 取得属于供货商的订单列表
	 * 
	 * @param currentPage
	 * @return 订单列表
	 */
	public QueryResult getServerOrderList(int pageNumber, String orderCode, String status, Integer companyId,
			List<CompanyToBuilding> buildingList, String startTime, String endTime,
			List<CompanyToSchoolUser> schoolUsers) {

		List<Integer> cids = new ArrayList<Integer>();
		// 物业管辖的用户
		List<Integer> userList = new ArrayList<Integer>();
		if (schoolUsers != null && schoolUsers.size() > 0) {
			for (CompanyToSchoolUser su : schoolUsers)
				userList.add(su.getUserId());
		} else {
			for (CompanyToBuilding com : buildingList)
				cids.add(com.getBuildingId());
			if (cids.size() > 0) {
				SqlExpressionGroup groupUser = Cnd.exps("buildingId", "in", cids);
				// 拼接条件
				List<ShopUserView> list = this.dao().query(ShopUserView.class, Cnd.where(groupUser), null);
				for (ShopUserView user : list)
					userList.add(user.getId());
			}
		}

		// 每页显示记录数
		int iPageSize = 15;

		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, iPageSize);

		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		// 拼接条件
		group = group.and("orderType", "=", Constants.SERVICE_ORDER);
		group = group.and("showFlag", "=", Constants.ORDER_STATUS_PAID);
		// 订单状态
		if (status != null && status.length() > 0) {
			group = group.and("status", "=", Integer.valueOf(status));
		}
		if (orderCode != null && orderCode.length() > 0) {
			group = group.and("orderCode", "like", "%" + orderCode + "%");
		}

		//若没有管辖用户就什么都不查
		if (userList.size() > 0) {
			group = group.and("userId", "in", userList);
		} else
			group = group.and("1", "=", 2);
		if (companyId != null && companyId != 0) {
			group = group.and("companyId", "=", companyId);
		}

		// 下单时间
		if (startTime != null && startTime.length() > 0) {
			group = group.and("createTime", ">", startTime + " 00:00:00");
		}

		// 下单时间
		if (endTime != null && endTime.length() > 0) {
			group = group.and("createTime", "<", endTime + " 23:59:59");
		}

		// 查询记录总数
		int recordCount = this.dao().count(ProductOrder.class, Cnd.where(group).desc("createTime"));
		pager.setRecordCount(recordCount);

		// 没数据，直接返回
		if (recordCount == 0) {
			return new QueryResult(new ArrayList<ProductOrder>(0), pager);
		}

		// 计算页数
		int maxPageNumber = 0;
		if (recordCount % Constants.LIST_ONE_PAGE_SIZE == 0) {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE;
		} else {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE + 1;
		}
		if (pageNumber > maxPageNumber) {
			pager.setPageNumber(maxPageNumber);
		}

		// 查询列表
		List<ProductOrder> orderList = this.dao().query(ProductOrder.class, Cnd.where(group).desc("createTime"), pager);

		// 返回
		return new QueryResult(orderList, pager);
	}

	public String getTradeSum(Integer userId, Integer tradeType) {
		String sql = "select sum(trade_money) from shop_users_trade where user_id=" + userId + " and trade_type="
				+ tradeType;
		SqlTemplate sqlTemplate = new SqlTemplate(this.dao());
		Double sum = sqlTemplate.queryForObject(sql, null, Double.class);
		if (sum == null)
			sum = 0d;
		DecimalFormat fnum = new DecimalFormat("##0.00");
		return fnum.format(sum);
	}

	public QueryResult getTradeList(int pageNumber, Integer userId, Integer tradeType) {

		if (pageNumber <= 0) {
			pageNumber = 1;
		}

		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		if (userId != null) {
			group = group.and("userId", "=", userId);
		}
		if (tradeType != null) {
			SqlExpressionGroup groupNew = Cnd.exps("tradeType", "=", tradeType).or("tradeType", "=",
					Constants.TRADE_TYPE_REBATE_COMANY);
			group = group.and(groupNew);
		}

		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, Constants.LIST_ONE_PAGE_SIZE);

		// 查询记录总数
		int recordCount = this.dao().count(ShopUsersTrade.class, Cnd.where(group));
		pager.setRecordCount(recordCount);

		// 没数据，直接返回
		if (recordCount == 0) {
			return new QueryResult(new ArrayList<ShopUsersTrade>(0), pager);
		}

		// 计算页数
		int maxPageNumber = 0;
		if (recordCount % Constants.LIST_ONE_PAGE_SIZE == 0) {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE;
		} else {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE + 1;
		}
		if (pageNumber > maxPageNumber) {
			pager.setPageNumber(maxPageNumber);
		}

		// 查询
		List<ShopUsersTrade> resultList = this.dao().query(ShopUsersTrade.class, Cnd.where(group).asc("createTime"),
				pager); // 当前页面显示记录

		// 返回
		return new QueryResult(resultList, pager);

	}

	/**
	 * 取得属于供货商的订单列表(已经完成的订单)
	 * 
	 * @param currentPage
	 * @return 订单列表
	 */
	public QueryResult getServerOrderListService(int pageNumber, String orderCode, String status, Integer companyId,
			List<CompanyToBuilding> buildingList, String startTime, String endTime,
			List<CompanyToSchoolUser> schoolUsers) {

		List<Integer> cids = new ArrayList<Integer>();
		// 物业管辖的用户
		List<Integer> userList = new ArrayList<Integer>();
		if (schoolUsers != null && schoolUsers.size() > 0) {
			for (CompanyToSchoolUser su : schoolUsers)
				userList.add(su.getUserId());
		} else {
			for (CompanyToBuilding com : buildingList)
				cids.add(com.getBuildingId());
			if (cids.size() > 0) {
				SqlExpressionGroup groupUser = Cnd.exps("buildingId", "in", cids);
				// 拼接条件
				List<ShopUserView> list = this.dao().query(ShopUserView.class, Cnd.where(groupUser), null);
				for (ShopUserView user : list)
					userList.add(user.getId());
			}
		}

		// 每页显示记录数
		int iPageSize = 15;

		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, iPageSize);

		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		// 拼接条件
		group = group.and("orderType", "=", Constants.SERVICE_ORDER);
		group = group.and("showFlag", "=", Constants.ORDER_STATUS_PAID);
		// 订单状态
		if (status != null && status.length() > 0) {
			group = group.and("status", "=", Integer.valueOf(status));
		}
		if (orderCode != null && orderCode.length() > 0) {
			group = group.and("orderCode", "like", "%" + orderCode + "%");
		}

		//若没有管辖用户就什么都不查
		if (userList.size() > 0) {
			group = group.and("userId", "in", userList);
		} else
			group = group.and("1", "=", 2);
		if (companyId != null && companyId != 0) {
			group = group.and("companyId", "=", companyId);
		}

		// 下单时间
		if (startTime != null && startTime.length() > 0) {
			group = group.and("createTime", ">", startTime + " 00:00:00");
		}

		// 下单时间
		if (endTime != null && endTime.length() > 0) {
			group = group.and("createTime", "<", endTime + " 23:59:59");
		}

		// 查询记录总数
		int recordCount = this.dao().count(ProductOrder.class, Cnd.where(group).desc("createTime"));
		pager.setRecordCount(recordCount);

		// 没数据，直接返回
		if (recordCount == 0) {
			return new QueryResult(new ArrayList<ProductOrder>(0), pager);
		}

		// 计算页数
		int maxPageNumber = 0;
		if (recordCount % Constants.LIST_ONE_PAGE_SIZE == 0) {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE;
		} else {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE + 1;
		}
		if (pageNumber > maxPageNumber) {
			pager.setPageNumber(maxPageNumber);
		}

		// 查询列表
		List<ProductOrder> orderList = this.dao().query(ProductOrder.class,
				Cnd.where(group).and("status", "=", Constants.ORDER_STATUS_DONE).desc("createTime"), pager);

		// 返回
		return new QueryResult(orderList, pager);
	}

	/**
	 * 根据金额取得返点百分比数字.
	 * 
	 * @param 金额
	 * @return 返点百分比数字
	 */
	public int getRebate(BigDecimal inMoney) {

		// 看金额在哪个区间内
		SqlExpressionGroup g1 = Cnd.exps("minMoney", "<=", inMoney).and("maxMoney", ">=", inMoney);
		RebateConfig config = this.dao().fetch(RebateConfig.class, Cnd.where(g1));

		// 不在任何一个区间内，认为无返点
		if (config == null) {
			return 0;
		}
		return config.getRebate();
	}

	/**
	 * 只查线上的
	 * @param companyId
	 * @param startTime
	 * @param endTime
	 * @param status
	 * @return
	 */
	public List<Supplier> getOrderSupplier(int companyId, String startTime, String endTime, Integer status) {
		String sql = "SELECT * FROM supplier s WHERE EXISTS ( SELECT v.id FROM order_supplier_view v WHERE v.supplier_id=s.id"
				+ " and exists(select id from company c where v.community_id = c.community_id and c.id=@cid) and v.show_flag="
				+ Constants.ORDER_STATUS_PAID + " and s.offline_flag=" + Constants.ON_LINE;
		Map<String, Object> param = Maps.newHashMap();
		param.put("cid", companyId);
		// 下单时间
		if (startTime != null && startTime.length() > 0) {
			sql += " and v.create_time > @startTime and v.create_time<@endTime";
			param.put("startTime", startTime + " 00:00:00");
			param.put("endTime", endTime + " 23:59:59");
		}
		// 订单状态
		if (status != null) {
			sql += " and v.status = @status";
			param.put("status", status);
		}
		sql += ")";
		SqlTemplate sqlTemplate = new SqlTemplate(this.dao());
		List<Supplier> result = sqlTemplate.query(sql, param, dao().getEntity(Supplier.class));
		return result;
	}

	public List<ReadyProductVO> getReceiveOrderList(int companyId, String startTime, String endTime, Integer status,
			String supplier) {
		String sql = "select * from order_supplier_view v where exists(select id from company c where v.community_id = c.community_id and c.id=@cid) and v.show_flag="
				+ Constants.ORDER_STATUS_PAID + " and v.virtual_flag=" + Constants.VIRTUAL_NO_FLAG;
		String sql2 = "select DISTINCT product_id from order_supplier_view v where exists(select id from company c where v.community_id = c.community_id and c.id=@cid) and v.show_flag="
				+ Constants.ORDER_STATUS_PAID + " and v.virtual_flag=" + Constants.VIRTUAL_NO_FLAG;
		Map<String, Object> param = Maps.newHashMap();
		param.put("cid", companyId);
		// 下单时间
		if (startTime != null && startTime.length() > 0) {
			sql += " and v.create_time > @startTime and v.create_time<@endTime";
			sql2 += " and v.create_time > @startTime and v.create_time<@endTime";
			param.put("startTime", startTime + " 00:00:00");
			param.put("endTime", endTime + " 23:59:59");
		}
		if (!StringUtils.isBlank(supplier)) {
			sql += " and v.supplier_id = @supplier";
			sql2 += " and v.supplier_id = @supplier";
			param.put("supplier", supplier);
		}
		// 订单状态
		if (status != null) {
			sql += " and v.status = @status";
			sql2 += " and v.status = @status";
			param.put("status", status);
		}
		SqlTemplate sqlTemplate = new SqlTemplate(this.dao());
		List<OrderSupplierView> result = sqlTemplate.query(sql, param, dao().getEntity(OrderSupplierView.class));
		List<Integer> productList = sqlTemplate.queryForList(sql2, null, param, Integer.class);
		List<ReadyProductVO> list = new ArrayList<ReadyProductVO>();
		for (Integer product : productList) {
			ReadyProductVO rpv = new ReadyProductVO();
			rpv.setProductId(product);
			rpv.setCommunityId(Integer.valueOf(supplier));
			int totalCount = 0;
			for (OrderSupplierView osv : result) {
				if (product.intValue() == osv.getProductId().intValue()) {
					rpv.setProductName(osv.getProductFieldValue() == null ? osv.getProductName() : osv.getProductName()
							+ "-" + osv.getProductFieldValue());
					rpv.setStockNumber(osv.getStockNumber());
					totalCount += osv.getProductCount();
				}
			}
			rpv.setProductCount(totalCount);
			list.add(rpv);
		}
		return list;
	}

	/**
	 * 取得属于供货商的订单列表
	 * 
	 * @param currentPage
	 * @return 订单列表
	 */
	public List<ProductOrder> getOrderUser(Integer status, List<CompanyToBuilding> buildingList,
			List<CompanyToSchoolUser> schoolUsers) {

		List<Integer> cids = new ArrayList<Integer>();
		// 物业管辖的用户
		List<Integer> userList = new ArrayList<Integer>();
		if (schoolUsers != null && schoolUsers.size() > 0) {
			for (CompanyToSchoolUser su : schoolUsers)
				userList.add(su.getUserId());
		} else {
			for (CompanyToBuilding com : buildingList)
				cids.add(com.getBuildingId());
			if (cids.size() > 0) {
				SqlExpressionGroup groupUser = Cnd.exps("buildingId", "in", cids);
				// 拼接条件
				List<ShopUserView> list = this.dao().query(ShopUserView.class, Cnd.where(groupUser), null);
				for (ShopUserView user : list)
					userList.add(user.getId());
			}
		}

		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		group = group.and("orderType", "=", Constants.PRODUCT_ORDER);
		group = group.and("showFlag", "=", Constants.ORDER_STATUS_PAID);
		// 订单状态
		if (status != null) {
			group = group.and("status", "=", status);
		}

		//若没有管辖用户就什么都不查
		if (userList.size() > 0) {
			group = group.and("userId", "in", userList);
		} else
			group = group.and("1", "=", 2);

		// 查询记录总数
		int recordCount = this.dao().count(ProductOrder.class, Cnd.where(group).desc("createTime"));

		// 没数据，直接返回
		if (recordCount == 0) {
			return null;
		}

		// 查询列表
		List<ProductOrder> orderList = this.dao().query(ProductOrder.class, Cnd.where(group).desc("createTime"), null);
		return orderList;
	}

	public void generalExcel(List<ProductOrder> orderList, OutputStream out) {

		SimpleDateFormat sdf = new SimpleDateFormat("SSS");
		String folder = String.valueOf(sdf.format(new Date())); // 登陆名后面加上传的日期

		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet createSheet = wb.createSheet();

			if (!orderList.isEmpty()) {
				// String[] array = head.toArray(new String[] {});
				Sheet sheet = wb.getSheetAt(0);
				if (sheet != null) {
					int rowIndex = 0;
					org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowIndex);

					Cell nameCell = row.createCell(0);
					nameCell.setCellValue("订单编号");
					Cell areaCell = row.createCell(1);
					areaCell.setCellValue("客户姓名");
					Cell noCell = row.createCell(2);
					noCell.setCellValue("订单时间");
					Cell cell3 = row.createCell(3);
					cell3.setCellValue("客户签字");

					for (ProductOrder order : orderList) {
						rowIndex++;
						org.apache.poi.ss.usermodel.Row rowNext = sheet.createRow(rowIndex);
						Cell unitCell = rowNext.createCell(0);
						unitCell.setCellValue(order.getOrderCode());
						Cell idCell = rowNext.createCell(1);
						ShopUser shopUser = this.dao().fetch(ShopUser.class, order.getUserId());
						idCell.setCellValue(shopUser.getRealName());
						Cell numCell = rowNext.createCell(2);
						numCell.setCellValue(sfd.format(order.getCreateTime()));
						Cell cell01 = rowNext.createCell(3);
					}
				}
			}
			wb.write(out);
		} catch (IOException e) {
			return;
		}
	}
}
